/*
 * Copyright (c) 2018, CENTRIN.CIYUN.LTD. All rights reserved.
 */
package com.cory.boot.component.easypoi;

import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.springframework.web.multipart.MultipartFile;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;

/**
 * Excel导出工具类
 * 
 * @date 2018年6月8日
 */

public class EasyExcelUtils {

    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, String title, HttpServletResponse response) {
        exportExcel(list, pojoClass, fileName, title, null, true, response);
    }

    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, String title, String sheetName,
            boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, String title, String sheetName,
            HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }
    
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null) {
            downLoadExcel(response, workbook, fileName);
        }
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
            ExportParams exportParams) {
        if (exportParams == null) {
            exportParams = new ExportParams();
        }
        exportParams.setDictHandler(new IExcelDictHandlerImpl());
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null){
            downLoadExcel(response, workbook, fileName);
        }
    }

    public static void downLoadExcel(HttpServletResponse response, Workbook workbook, String fileName) {
        try {
            if (fileName.indexOf(".") < 0) {
                fileName += ".xlsx";
            }
            
            response.setHeader("progma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new RuntimeException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
        return list;
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows,
            Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new RuntimeException("excel文件不能为空");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
        return list;
    }
    
    
    public static void exportExcelTemplate(Map<String, Object> map, TemplateExportParams params, String fileName,
            HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        if (workbook != null) {
            downLoadExcel(response, workbook, fileName);
        }
    }

    /**
     * 添加下拉验证
     * @param sheet
     * @param datas 下拉数据
     * @param row 第几行开始  0开始
     * @param col 第几列开始  0开始
     * @author xiongcong 2022-07-19
     */
    public static void createDataSelect(Sheet sheet, String[] datas, int row, int col, String tips) {
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(row, 65535, col, col);
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(datas);
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        //处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        dataValidation.setEmptyCellAllowed(true);
        dataValidation.setShowPromptBox(true);
        dataValidation.createPromptBox("提示", StringUtils.defaultIfBlank(tips, "只能选择下拉框里面的数据"));
        sheet.addValidationData(dataValidation);
    } 
    
    /**
     * 添加下拉验证，用于下拉内容很多，字符超过255
     * @param sheet
     * @param datas 下拉数据
     * @param sheetIndex 影藏的sheet编号（例如1,2,3），多个下拉数据不能使用同一个
     * @param row 第几行开始  0开始
     * @param col 第几列开始  0开始
     * @author xiongcong 2022-07-19
     */
    public static void createLargeDataSelect(Workbook workbook, Sheet sheet, String[] datas, int sheetIndex, int row, int col, String tips) {
        String sheetName="sheetName"+String.valueOf(sheetIndex);
        Sheet hidden = workbook.createSheet(sheetName);
        for (int i = 0; i < datas.length; i++) {
            hidden.createRow(i).createCell(0).setCellValue(datas[i]);
        }
        String refers = sheetName+"!$A$1:$A$" + datas.length;
        
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(row, 65535, col, col);
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        //处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        dataValidation.setEmptyCellAllowed(true);
        dataValidation.setShowPromptBox(true);
        dataValidation.createPromptBox("提示", StringUtils.defaultIfBlank(tips, "只能选择下拉框里面的数据"));
        sheet.addValidationData(dataValidation);
        
        workbook.setSheetHidden(sheetIndex, true);
    }
}
